﻿Create database QLVBDQ
go
use QLVBDQ
go
create table KHACHHANG
(
		MaKhachHang nvarchar(10),
		TenKhachHang nvarchar(40),
		DiaChi nvarchar(50),
		LoaiKhachHang int,
		constraint PK_KHACHHANG primary key  (MaKhachHang)
)

create table SANPHAM
(
		MaSanPham nvarchar(10),
		TenSanPham nvarchar(40),
		MaLoaiSanPham nvarchar(10),
		DonGia money,
		SoLuongTon int,
		constraint PK_SANPHAM primary key (MaSanPham)
)

Create table PHIEUNO
(
		MaPhieuNo nvarchar(10),
		MaPhieuBanHang nvarchar(10),
		NgayNo smalldatetime,
		SoTienTraNo money,
		NgayTra smalldatetime,
		SoTienTra money,
		ConLai money
		constraint PK_PHIEUNO primary key (MaPhieuNo)
)

create table PHIEUBANHANG
(
		MaPhieuBanHang nvarchar(10),
		NgayBan smalldatetime,
		NgayThanhToan smalldatetime,
		MaKhachHang nvarchar(10),
		TongTien money,
		constraint PK_PHIEUBANHANG primary key (MaPhieuBanHang)
)

create table CTPHIEUBANHANG
(
		MaCTPhieuBanHang nvarchar(10),
		MaSanPham nvarchar(10),
		MaPhieuBanHang nvarchar(10),
		SoLuong int, 
		DonGia money,
		constraint PK_CTPHIEUBANHANG primary key (MaCTPhieuBanHang)
)

create table LOAISANPHAM
(
		MaLoaiSanPham nvarchar(10),
		TenLoaiSanPham nvarchar(40),
		constraint PK_LOAISANPHAM primary key (MaLoaiSanPham)
)

create table DICHVU
(
		MaDV nvarchar(10),
		TenDV nvarchar(40),
		DonGia money,
		constraint PK_DICHVU primary key (MaDV)
)

create table PHIEUDICHVU
(
		MaPhieuDV nvarchar(10),
		NgayDK smalldatetime,
		NgayGiao smalldatetime,
		MaKhachHang nvarchar(10),
		TongTien money,
		constraint PK_PHIEUDICHVU primary key (MaPhieuDV)
)

create table CHITIETPHIEUDICHVU
(
		MaCTPhieuDV nvarchar(10),
		MaPhieuDV nvarchar(10),
		MaDV nvarchar(10), 
		SoLuong int,
		DonGia money,
		constraint PK_CHITIETPHIEUDICHVU primary key (MaCTPhieuDV)
)

create table PHIEUMUA
(
		MaPhieuMua nvarchar(10),
		NgayMua smalldatetime,
		NgayThanhToan smalldatetime,
		MaKhachHang nvarchar(10),
		TongTien money,
		constraint PK_PHIEUMUA primary key (MaPhieuMua)
)

create table CHITIETPHIEUMUA
(
		MaCTPhieuMua nvarchar(10),
		MaPhieuMua nvarchar(10),
		MaSanPham nvarchar(10),
		SoLuong int,
		DonGia money,
		constraint PK_CHITIETPHIEUMUA primary key (MaCTPhieuMua)
)

create table HANGGIACONG
(
		MaHangGC nvarchar(10),
		TenhangGC nvarchar(40),
		DonGia money,
		constraint PK_HANGGIACONG primary key (MaHangGC)
)

create table PHIEUGIACONG
(
		MaPhieuGC nvarchar(10),
		NgayGC smalldatetime,
		NgayNhanHang smalldatetime,
		NgayThanhToan smalldatetime,
		TongTien money,
		MaTho nvarchar(10),
		constraint PK_PHIEUGIACONG primary key (MaPhieuGC)
)

create table CTPHIEUGIACONG
(
		MaCTPhieuGC nvarchar(10),
		MaPhieuGC nvarchar(10),
		MaHangGC nvarchar(10),
		SoLuong int,
		DonGia money,
		constraint PK_CTPHIEUGIACONG primary key (MaCTPhieuGC)
)

create table THO
(
		MaTho nvarchar(10),
		TenTho nvarchar(40),
		DiaChi nvarchar(100),
		constraint PK_THO primary key (MaTho)
)

create table PHIEUCHI
(
		MaPhieuChi nvarchar(10),
		NgayChi smalldatetime,
		TongTien money,
		constraint PK_PHIEUCHI primary key (MaPhieuChi)
)

create table CHITIETPHIEUCHI
(
		MaCTPhieuChi nvarchar(10),
		MaPhieuChi nvarchar(10),
		MaPhi nvarchar(10),
		ThanhTien money,
		constraint PK_CHITIETPHIEUCHI primary key (MaCTPhieuChi)
)

create table PHI
(
		MaPhi nvarchar(10),
		TenPhi nvarchar(100),
		constraint PK_PHI primary key (MaPhi)
)

create table BAOCAOTON
(
		MaBaoCaoTon nvarchar(10),
		NgayBaoCao smalldatetime,
		constraint PK_BAOCAOTON primary key (MaBaoCaoTon)
)

create table CTBAOCAOTON
(
		MaCTBaoCaoTon nvarchar(10),
		MaBaoCaoTon nvarchar(10),
		MaSanPham nvarchar(10),
		TonDau int,
		PhatSinh int,
		TonCuoi int,
		SoLuongBan int,
		SoLuongMua int,
		constraint PK_CTBAOCAOTON primary key (MaCTBaoCaoTon)
)

create table BAOCAOCONGNO
(
		MaBaoCaoCongNo nvarchar(10),
		NgayBaoCao smalldatetime,
		constraint PK_BAOCAOCONGNO primary key (MaBaoCaoCongNo)
)

create table CTBAOCAOCONGNO
(
	MaCTBaoCaoCongNo nvarchar(10),
	MaBaoCaoCongNo nvarchar(10),
	MaPhieuNo nvarchar(10),
	TienNo money,
	constraint PK_CTBAOCAOCONGNO primary key (MaCTBaoCaoCongNo)
)

---khoa ngoai

alter table PHIEUNO add constraint FK_PHIEUNO_MAPHIEUBAN foreign key (MaPhieuBanHang) references PHIEUBANHANG(MaPhieuBanHang)
alter table PHIEUBANHANG add constraint FK_PHIEUBANHANG_MAKHACHHANG foreign key (MaKhachHang) references KHACHHANG(MaKhachHang)
alter table CTPHIEUBANHANG add constraint FK_CTPHIEUBANHANG_MAPHIEUBAN foreign key (MaPhieuBanHang) references PHIEUBANHANG(MaPhieuBanHang)
alter table CTPHIEUBANHANG add constraint FK_CTPHIEUBANHANG_MASANPHAM foreign key (MaSanPham) references SANPHAM(MaSanPham)
alter table SANPHAM add constraint FK_SANPHAM_MASANPHAM foreign key (MaLoaiSanPham) references LOAISANPHAM(MaLoaiSanPham)
alter table CHITIETPHIEUDICHVU add constraint FK_CHITIETPHIEUDV_MAPHIEUDV foreign key (MaPhieuDV) references PHIEUDICHVU(MaPhieuDV)
alter table CHITIETPHIEUDICHVU add constraint FK_CHITIETPHIEUDV_MADV foreign key (MaDV) references DICHVU(MaDV)
alter table CHITIETPHIEUMUA add constraint FK_CHITIETPHIEUMUA_MASANPHAM foreign key (MaSanPham) references SANPHAM(MaSanPham)
alter table CHITIETPHIEUMUA add constraint FK_CHITIETPHIEUMUA_MAPHIEUMUA foreign key (MaPhieuMua) references PHIEUMUA(MaPhieuMua)
alter table PHIEUMUA add constraint FK_PHIEUMUA_MAKHACHHANG foreign key (MaKhachHang) references KHACHHANG(MaKhachHang)
 alter table CTPHIEUGIACONG add constraint FK_CTPHIEUGIACONG_MAHANGGC foreign key (MaHangGC) references HANGGIACONG(MaHangGC)
 alter table CTPHIEUGIACONG add constraint FK_CTPHIEUGIACONG_MAPHIEUGC foreign key (MaPhieuGC) references PHIEUGIACONG(MaPhieuGC)
 alter table PHIEUGIACONG add constraint FK_PHIEUGIACONG_MATHO foreign key (MaTho) references THO(MaTho)
 alter table CHITIETPHIEUCHI add constraint FK_CHITIETPHIEUCHI_MAPHIEUCHI foreign key (MaPhieuChi) references PHIEUCHI(MaPhieuChi)
 alter table CHITIETPHIEUCHI add constraint FK_CHITIETPHIEUCHI_MAPHI foreign key (MaPhi) references PHI(MaPhi)
 alter table CTBAOCAOTON add constraint FK_CTBAOCAOTON_MABAOCAOTON foreign key (MaBaoCaoTon) references BAOCAOTON(MaBaoCaoTon) 
 alter table CTBAOCAOTON add constraint FK_CTBAOCAOTON_MASANPHAM foreign key (MaSanPham) references SANPHAM(MaSanPham)
 alter table CTBAOCAOCONGNO add constraint FK_CTBAOCAOCONGNO_MABAOCAOCONGNO foreign key (MaBaoCaoCongNo) references BAOCAOCONGNO(MaBaoCaoCongNo) 
 alter table CTBAOCAOCONGNO add constraint FK_CTBAOCAOCONGNO_MAPHIEUNO foreign key (MaPhieuNo) references PHIEUNO(MaPhieuNo)


 set dateformat dmy;

 delete [PHI];
 delete [LOAISANPHAM];
 delete [THO];
 delete [HANGGIACONG];
 delete [DICHVU];
 delete [KHACHHANG];
 delete [SANPHAM];

 -- làm cái proceduce


 --select * from [PHIEUBANHANG] P where P.MaPhieuBanHang in (select [MaPhieuBanHang] from [PHIEUNO])
 --select * from [PHIEUNO] p WHERE p.MaPhieuBanHang = 'PB004' and MaPhieuNo = (select MAX(MaPhieuNo) from [PHIEUNO] p WHERE p.MaPhieuBanHang = 'PB004')

  ---- nhập dữ liệu đê... zzzzzzzzzzzzzzzzzz

-----------------------------Bảng Thợ---------------------------------------------------
---- drop proc ADD_THO
 print 'Them Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_THO' and type = 'P')
EXEC
('
	CREATE PROC ADD_THO (@MaTho nvarchar(10), @TenTho nvarchar(40), @DiaChi nvarchar(100))
	AS
	BEGIN
		INSERT INTO [THO] VALUES (@MaTho, @TenTho, @DiaChi);
	END
');
GO

---- drop proc ADD_THO_MOI
 print 'Them Tho moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_THO_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_THO_MOI (@TenTho nvarchar(40), @DiaChi nvarchar(100))
	AS
	declare @Matho nvarchar(10) = null;
	exec @Matho = NEXT_F THO;
	BEGIN
		exec ADD_THO @Matho, @TenTho,@DiaChi;
	END
');
GO
-----exec ADD_THO_MOI 'asdasd','asdasdasd';



---- drop proc REMOVE_THO
 print 'XOA Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'REMOVE_THO' and type = 'P')
EXEC
('
	CREATE PROC REMOVE_THO (@MaTho nvarchar(10))
	AS
	BEGIN
		DELETE [THO] WHERE [THO].MaTho = @MaTho;
	END
');
GO
----REMOVE_THO 'TH001';
---- drop proc UPDATE_THO
 print 'SUA Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'UPDATE_THO' and type = 'P')
EXEC
('
	CREATE PROC UPDATE_THO (@MaTho nvarchar(10), @TenTho nvarchar(40), @DiaChi nvarchar(100))
	AS
	BEGIN
		update [THO] set TenTho = @TenTho, DiaChi = @DiaChi where MaTho = @MaTho;
	END
');
GO

----UPDATE_THO 'TH002','TAmoasd','Tao sua';

---- drop proc LAYTHOBYID
 print 'Lay Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAYTHOBYID' and type = 'P')
EXEC
('
	CREATE PROC LAYTHOBYID (@MaTho nvarchar(10))
	AS
	BEGIN
		select * from [THO] where MaTho = @MaTho;
	END
');
GO
 ----exec LAYTHOBYID 'TH001';
---- drop proc LAYTOANBOTHO
 print 'Lay toan bo tho Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAYTOANBOTHO' and type = 'P')
EXEC
('
	CREATE PROC LAYTOANBOTHO 
	AS
	BEGIN
		select * from [THO];
	END
');
GO


---- drop proc LAYTOANBOMATHO

print 'Lay Toan Bo Ma Tho'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAYTOANBOMATHO' and type = 'P')
EXEC
('
	CREATE PROC LAYTOANBOMATHO 
	AS
	BEGIN
		select [MaTho] from [THO];
	END
');
GO

--- exec LAYTOANBOMATHO;


---exec LAYTOANBOTHO;


--- drop proc FIND_THO

 print 'Tim tho tho Tho'

 go
CREATE PROC FIND_THO  (@MaTho nvarchar(10), @TenTho nvarchar(40) ,@DiaChi nvarchar(100))
AS

BEGIN
declare @search nvarchar(100) = null;
declare @and char(10) = 'and ';
declare @result nvarchar(1000) = null;
declare @space char(2) = null;
declare @per nvarchar(4) = '%';
		if	(LEN(@MaTho) <> 0 )
		if(LEN(@search) <> 0 )
			set @search = '[MaTho] like  N''%'+@MaTho+'%'' ';
		else
			set @search =  '[MaTho] like  N''%'+@MaTho+'%'' ';
		if	(LEN(@TenTho) <> 0 )
		if(LEN(@search)<>0)
			set @search = @search + @and + ' [TenTho]  like N''%'+@TenTho+'%'' ';
		else
			set @search = ' [TenTho]  like N''%'+@TenTho+'%'' ';
		if	(LEN(@DiaChi) <> 0 )
			if(LEN(@search)<>0)
			set @search = @search + @and + ' [DiaChi]  like N''%'+@DiaChi+'%'' ';
			else
			 set @search = ' [DiaChi]  like N''%'+@DiaChi+'%'' ';
		if(LEN(@search)<>0)
			set @result = N'Select * from [THO] where '+@search;
		else
			set @result = N'Select * from [THO]';
		exec (@result);		  
	END
go



-------------------------------------------------------------------------------------------------------------------
-----------------------------Dịch Vụ---------------------------------------------------
------- drop proc ADD_DICHVU
print 'Them Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_DICHVU' and type = 'P')
EXEC
('
	CREATE PROC ADD_DICHVU (@MaDV nvarchar(10), @TenDV nvarchar(40), @dongia money)
	AS
	BEGIN
		INSERT INTO [DICHVU] VALUES (@MaDV, @TenDV, @dongia);
	END
');
GO

print 'Them dich vu moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_DICHVU_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_DICHVU_MOI (@tendichvu nvarchar(40), @dongia money)
	AS
	declare @madichvu nvarchar(10) = null;
	exec @madichvu = NEXT_F DICHVU;
	BEGIN
		exec ADD_DICHVU @madichvu, @tendichvu, @dongia;
	END
');
GO

------- drop proc REMOVE_DICHVU
print 'Xoa Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'REMOVE_DICHVU' and type = 'P')
EXEC
('
	CREATE PROC REMOVE_DICHVU (@MaDV nvarchar(10))
	AS
	BEGIN
		delete [DICHVU] where [DICHVU].MaDV = @MaDV;
	END
');
GO

----REMOVE_DICHVU 'DV001';

-------------- drop proc UPDATE_DICHVU
print 'Sua Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'UPDATE_DICHVU' and type = 'P')
EXEC
('
	CREATE PROC UPDATE_DICHVU (@MaDV nvarchar(10), @TenDV nvarchar(40), @dongia money)
	AS
	BEGIN
		update [DICHVU] set [TenDV]=@TenDV, [DonGia]=@dongia where [DICHVU].MaDV = @MaDV;
	END
');
GO

----UPDATE_DICHVU 'DV002', 'Tao moi sua',100;

------ drop proc LAYDICHVUBYID
print 'lay Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAYDICHVUBYID' and type = 'P')
EXEC
('
	CREATE PROC LAYDICHVUBYID (@MaDV nvarchar(10))
	AS
	BEGIN
		select * from [DICHVU] where  [DICHVU].MaDV = @MaDV;
	END
');
GO
---exec LAYDICHVUBYID 'DV001';

print 'lay toan bo Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAY_TOANBO_DICHVU' and type = 'P')
EXEC
('
	CREATE PROC LAY_TOANBO_DICHVU
	AS
	BEGIN
		select * from [DICHVU];
	END
');
GO

print 'lay toan bo Ma Dich vu'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAY_TOANBO_MADICHVU' and type = 'P')
EXEC
('
	CREATE PROC LAY_TOANBO_MADICHVU
	AS
	BEGIN
		select MaDV from [DICHVU];
	END
');
GO

-------------------------------------------------------------------------------------------------------------------
-----------------------------Khách Hàng---------------------------------------------------
---- drop proc ADD_KHACHHANG
print 'Them Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_KHACHHANG' and type = 'P')
EXEC
('
	CREATE PROC ADD_KHACHHANG(@MaKhachHang nvarchar(10), @TenKhachHang nvarchar(40), @DiaChi nvarchar(50),@LoaiKhachHang int)
	AS
	BEGIN
		INSERT INTO [KHACHHANG] VALUES (@MaKhachHang, @TenKhachHang, @DiaChi,@LoaiKhachHang);
	END
');
GO

---- drop proc ADD_THO_MOI
 print 'Them Khach Hang Moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_KHACHHANG_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_KHACHHANG_MOI (@TenKhachHang nvarchar(40), @DiaChi nvarchar(50),@LoaiKhachHang int)
	AS
	declare @MaKhachHang nvarchar(10) = null;
	exec @MaKhachHang = NEXT_F KHACHHANG;
	BEGIN
		exec ADD_KHACHHANG @MaKhachHang, @TenKhachHang, @DiaChi, @LoaiKhachHang;
	END
');
GO

---- drop proc REMOVE_KHACHHANG
print 'Xoa Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'REMOVE_KHACHHANG' and type = 'P')
EXEC
('
	CREATE PROC REMOVE_KHACHHANG(@MaKhachHang nvarchar(10))
	AS
	BEGIN
		delete [KHACHHANG] where MaKhachHang = @MaKhachHang;
	END
');
GO
----REMOVE_KHACHHANG 'KH001';
---- drop proc UPDATE_KHACHHANG
print 'Sua Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'UPDATE_KHACHHANG' and type = 'P')
EXEC
('
	CREATE PROC UPDATE_KHACHHANG(@MaKhachHang nvarchar(10), @TenKhachHang nvarchar(40), @DiaChi nvarchar(50),@LoaiKhachHang int)
	AS
	BEGIN
		update [KHACHHANG] set TenKhachHang = @TenKhachHang, DiaChi = @DiaChi , LoaiKhachHang = @LoaiKhachHang where MaKhachHang = @MaKhachHang;
	END
');
GO

---UPDATE_KHACHHANG 'KH002', '','';
----- drop proc LAYKHACHHANGBYID

print 'Lay Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAYKHACHHANGBYID' and type = 'P')
EXEC
('
	CREATE PROC LAYKHACHHANGBYID(@MaKhachHang nvarchar(10))
	AS
	BEGIN
		select * from [KHACHHANG] where MaKhachHang = @MaKhachHang;
	END
');
GO

print 'Lay toan bo Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAY_TOANBO_KHACHHANG' and type = 'P')
EXEC
('
	CREATE PROC LAY_TOANBO_KHACHHANG
	AS
	BEGIN
		select * from [KHACHHANG] ;
	END
');
GO

print 'Lay toan bo Ma Khach Hang'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAY_TOANBO_MAKHACHHANG' and type = 'P')
EXEC
('
	CREATE PROC LAY_TOANBO_MAKHACHHANG
	AS
	BEGIN
		select MaKhachHang from [KHACHHANG] ;
	END
');
GO

print 'Lay toan bo Ma Khach Hang Quen'
IF not exists (SELECT * FROM sys.objects WHERE name = 'LAY_TOANBO_MAKHACHHANG_QUEN' and type = 'P')
EXEC
('
	CREATE PROC LAY_TOANBO_MAKHACHHANG_QUEN
	AS
	BEGIN
		select MaKhachHang from [KHACHHANG] k where k.LoaiKhachHang = 1;
	END
');
GO
---exec LAY_TOANBO_MAKHACHHANG;
----exec LAYKHACHHANGBYID 'KH001';


----- drop proc FIND_KHACHHANG
print 'Tim Khach Hang'
go

create proc FIND_KHACHHANG(@MaKhachHang nvarchar(10), @TenKhachHang nvarchar(40), @DiaChi nvarchar(50),@LoaiKhachHang nvarchar(5))
as
begin 
declare @search nvarchar(1000) = '';
declare @and char(10) = 'and ';
declare @result nvarchar(1000) = null;
declare @space char(2) = null;
declare @per nvarchar(4) = '%';
	if(LEN(@MaKhachHang) <>0)
		if(LEN(@search)<>0)
			set @search = ' [MaKhachHang] like N''%'+@MaKhachHang+'%'' ';
		else
			set @search = ' [MaKhachHang] like N''%'+@MaKhachHang+'%'' ';
	if(LEN(@TenKhachHang)<> 0 )
		if(LEN(@search)<>0)
			set @search = @search + @and + ' [TenKhachHang] like N''%'+@TenKhachHang+'%'' ';
		else
			set @search = ' [TenKhachHang] like N''%'+@TenKhachHang+'%'' ';
	if	(LEN(@DiaChi) <> 0 )
		if(LEN(@search) <>0)
			set @search  =@search + @and + ' [DiaChi] like N''%' +@DiaChi+'%'' ';
		else
			set @search  =' [DiaChi] like N''%' +@DiaChi+'%'' ';
	if(LEN(@LoaiKhachHang)<>0)
		if(LEN(@search)<>0)
			set	@search = @search + @and + ' [LoaiKhachHang] = '+@LoaiKhachHang;
		else
				set	@search =  ' [LoaiKhachHang] = '+@LoaiKhachHang;
	if(LEN(@search)<>0)
		set @result = N'Select * from [KHACHHANG] where '+@search;
	else
		set @result = N'Select * from [KHACHHANG]';
exec (@result);
end
go


---exec FIND_KHACHHANG 'KH024','4',N'Quận',0;



-------------------------------------------------------------------------------------------------------------------
-----------------------------Hàng Gia Công---------------------------------------------------
--- drop proc ADD_HANGGIACONG
print 'Them HangGiaCong'
if	not exists (select * from sys.objects where name='ADD_HANGGIACONG' and type='P')
exec
('
		create proc ADD_HANGGIACONG (@mahanggiacong nvarchar(10), @tenhanggiacong nvarchar(40), @dongia money)
		as
		begin
				insert into [HANGGIACONG] values (@mahanggiacong, @tenhanggiacong, @dongia);
		end
') 
Go

--- drop proc ADD_HANGGIACONG_MOI
print 'Them HangGiaCong'
if	not exists (select * from sys.objects where name='ADD_HANGGIACONG_MOI' and type='P')
exec
('
		create proc ADD_HANGGIACONG_MOI (@tenhanggiacong nvarchar(40), @dongia money)
		as
		begin
		declare @mahanggiacong nvarchar(10) = null;
		exec @mahanggiacong = NEXT_F HANGGIACONG;
				insert into [HANGGIACONG] values (@mahanggiacong, @tenhanggiacong, @dongia);
		end
') 
Go

--- drop proc REMOVE_HANGGIACONG
print 'Xoa HangGiaCong'
if	not exists (select * from sys.objects where name='REMOVE_HANGGIACONG' and type='P')
exec
('
		create proc REMOVE_HANGGIACONG (@mahanggiacong nvarchar(10))
		as
		begin
				delete [HANGGIACONG] where MaHangGC = @mahanggiacong;
		end
') 
Go
---REMOVE_HANGGIACONG 'GC001';
--- drop proc UPDATE_HANGGIACONG
print N'sửa HangGiaCong'
if	not exists (select * from sys.objects where name='UPDATE_HANGGIACONG' and type='P')
exec
('
		create proc UPDATE_HANGGIACONG (@mahanggiacong nvarchar(10), @tenhanggiacong nvarchar(40), @dongia money)
		as
		begin
				update [HANGGIACONG] set TenHangGC = @tenhanggiacong, DonGia = @dongia where MaHangGC = @mahanggiacong;
		end
') 
Go
----UPDATE_HANGGIACONG 'GC002','rtrt',0;
--- select * from HANGGIACONG

---- drop proc LAYHANGGIACONGBYID
print N'lay LAYHANGGIACONGBYID'
if	not exists (select * from sys.objects where name='LAYHANGGIACONGBYID' and type='P')
exec
('
		create proc LAYHANGGIACONGBYID (@mahanggiacong nvarchar(10))
		as
		begin
				select *from [HANGGIACONG] where MaHangGC = @mahanggiacong;
		end
') 
Go

--drop proc LAYTOANBOMAHANGGIACONG
print N'lay LAYTOANBOMAHANGGIACONG'
if	not exists (select * from sys.objects where name='LAYTOANBOMAHANGGIACONG' and type='P')
exec
('
		create proc LAYTOANBOMAHANGGIACONG 
		as
		begin
				select [MaHangGC] from [HANGGIACONG] ;
		end
') 
Go

-- exec LAYTOANBOMAHANGGIACONG;

---- drop proc LAYHANGGIACONG
print N'lay HangGiaCong'
if	not exists (select * from sys.objects where name='LAYHANGGIACONG' and type='P')
exec
('
		create proc LAYHANGGIACONG 
		as
		begin
				select *from [HANGGIACONG] ;
		end
') 
Go
---exec LAYHANGGIACONGBYID 'GC001';


-------------------------------------------------------------------------------------------------------------------
-----------------------------Loại Sản Phẩm---------------------------------------------------
--- drop proc ADD_LOAISANPHAM
print 'Them LoaiSanPham'
if	not exists (select * from sys.objects where name='ADD_LOAISANPHAM' and type='P')
exec
('
		create proc ADD_LOAISANPHAM(@maloaisp nvarchar(10), @tenloaisp nvarchar(40))
		as
		begin
				insert into [LOAISANPHAM] values (@maloaisp, @tenloaisp);
		end
') 
Go

--- drop proc ADD_LOAISANPHAM_MOI
print 'Them LoaiSanPham moi'
if	not exists (select * from sys.objects where name='ADD_LOAISANPHAM_MOI' and type='P')
exec
('
		create proc ADD_LOAISANPHAM_MOI(@tenloaisp nvarchar(40))
		as
		begin
		declare @Masp nvarchar(10)=null;
		exec @Masp = NEXT_F LOAISANPHAM;
				insert into [LOAISANPHAM] values (@Masp, @tenloaisp);
		end
') 
Go

--- drop proc REMOVE_LOAISANPHAM
print 'XOA LoaiSanPham'
if	not exists (select * from sys.objects where name='REMOVE_LOAISANPHAM' and type='P')
exec
('
		create proc REMOVE_LOAISANPHAM(@maloaisp nvarchar(10))
		as
		begin
				delete [LOAISANPHAM] where MaLoaiSanPham =@maloaisp;
		end
') 
Go
---REMOVE_LOAISANPHAM 'SP001';
--- drop proc UPDATE_LOAISANPHAM
print 'SUA LoaiSanPham'
if	not exists (select * from sys.objects where name='UPDATE_LOAISANPHAM' and type='P')
exec
('
		create proc UPDATE_LOAISANPHAM(@maloaisp nvarchar(10), @tenloaisp nvarchar(40))
		as
		begin
			update [LOAISANPHAM] set TenLoaiSanPham = @tenloaisp where MaLoaiSanPham = @maloaisp;
		end
') 
Go

---UPDATE_LOAISANPHAM 'SP002','';

-- drop proc LAYLOAISANPHAMBYID
print 'lay  LoaiSanPham'
if	not exists (select * from sys.objects where name='LAYLOAISANPHAMBYID' and type='P')
exec
('
		create proc LAYLOAISANPHAMBYID(@maloaisp nvarchar(10))
		as
		begin
			select * from [LOAISANPHAM] where MaLoaiSanPham = @maloaisp;
		end
') 
Go

--exec LAYLOAISANPHAMBYID 'LSP001';

print 'lay toan bo LoaiSanPham'
if	not exists (select * from sys.objects where name='LAY_TOANBO_LOAISANPHAM' and type='P')
exec
('
		create proc LAY_TOANBO_LOAISANPHAM
		as
		begin
			select * from [LOAISANPHAM];
		end
') 
Go

--LAY_TOANBO_LOAISANPHAM

-------------------------------------------------------------------------------------------------------------------
-----------------------------Sản phẩm---------------------------------------------------
-- MaSanPham nvarchar(10),
--		TenSanPham nvarchar(40),
--		MaLoaiSanPham nvarchar(10),
--		DonGia money,
--		SoLuongTon int,
--- drop proc ADD_SANPHAM
print 'Them SanPham'
if	not exists (select * from sys.objects where name='ADD_SANPHAM' and type='P')
exec
('
		create proc ADD_SANPHAM (@masanpham nvarchar(10), @tensanpham nvarchar(40), @maloaisanpham nvarchar(10), @dongia money, @soluongton int )
		as
		begin
				insert into [SANPHAM] values (@masanpham, @tensanpham,@maloaisanpham,@dongia,@soluongton );
		end
') 
Go
---exec ADD_SANPHAM 'SP001' ,N'Vàng 18k' ,'LSP001',10000,100;

print 'Them San pham moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_SANPHAM_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_SANPHAM_MOI (@tensanpham nvarchar(40), @maloaisanpham nvarchar(10), @dongia money, @soluongton int)
	AS
	declare @masanpham nvarchar(10) = null;
	exec @masanpham = NEXT_F SANPHAM;
	BEGIN
		exec ADD_SANPHAM @masanpham, @tensanpham, @maloaisanpham, @dongia, @soluongton;
	END
');
GO

--DROP PROC ADD_SANPHAM_MOI

--exec ADD_SANPHAM_MOI '234234', 'LSP001', 100, 200;

--- drop proc REMOVE_SANPHAM
print 'xoa SanPham'
if	not exists (select * from sys.objects where name='REMOVE_SANPHAM' and type='P')
exec
('
		create proc REMOVE_SANPHAM (@masanpham nvarchar(10))
		as
		begin
				delete [SANPHAM] where MaSanPham = @masanpham;
		end
') 
Go
--- drop proc UPDATE_SANPHAM
print 'sua LoaiSanPham'
if	not exists (select * from sys.objects where name='UPDATE_SANPHAM' and type='P')
exec
('
		create proc UPDATE_SANPHAM (@masanpham nvarchar(10), @tensanpham char (40), @maloaisanpham nvarchar(10), @dongia money, @soluongton int )
		as
		begin
				update [SANPHAM] set TenSanPham=@tensanpham, MaLoaiSanPham=@maloaisanpham, DonGia = @dongia, SoLuongTon = @soluongton where MaSanPham = @masanpham;
		end
') 
Go


-- drop proc LAYSANPHAMBYID
print 'lay san pham'
if	not exists (select * from sys.objects where name='LAYSANPHAMBYID' and type='P')
exec
('
create proc LAYSANPHAMBYID (@masanpham nvarchar(10))
as
begin 
	select  * from [SANPHAM],[LOAISANPHAM] where [SANPHAM].MaSanPham = @masanpham and [SANPHAM].MaLoaiSanPham = [LOAISANPHAM].MaLoaiSanPham;
end
')
go

---exec LAYSANPHAMBYID 'SP001';

print 'lay toan bo san pham'
if	not exists (select * from sys.objects where name='LAY_TOANBO_SANPHAM' and type='P')
exec
('
create proc LAY_TOANBO_SANPHAM
as
begin 
	select  * from [SANPHAM],[LOAISANPHAM] where [SANPHAM].MaLoaiSanPham = [LOAISANPHAM].MaLoaiSanPham;
end
')
go

print 'lay toan bo ma san pham'
if	not exists (select * from sys.objects where name='LAY_TOANBO_MASANPHAM' and type='P')
exec
('
create proc LAY_TOANBO_MASANPHAM
as
begin 
	select MaSanPham from [SANPHAM];
end
')
go


---
-- exec LAY_TOANBO_MASANPHAM;
--LAY_TOANBO_SANPHAM
--select  * from [SANPHAM],[LOAISANPHAM] where [SANPHAM].MaLoaiSanPham = [LOAISANPHAM].MaLoaiSanPham

-- drop proc FIND_SANPHAM

print 'Tim San Pham'

go

create proc FIND_SANPHAM (@MaSanPham nvarchar(10), @TenSanPham nvarchar(40), @TenLoaiSanPham nvarchar(100), @DonGia nvarchar(10), @SoLuongTon nvarchar(10))
as
begin 
declare @search nvarchar(1000) = null;
declare @and char(10) = 'and ';
declare @result nvarchar(1000) = null;
declare @space char(2) = null;
declare @per nvarchar(4) = '%';
	if(LEN(@MaSanPham)<>0)
		if(LEN(@search) <>0)
			set @search = @and +' [MaSanPham] like N''%' + @MaSanPham + '%'' ';
		else
			set @search = @and + ' [MaSanPham] like N''%' + @MaSanPham + '%'' ';

	if(LEN(@TenLoaiSanPham)<>0)
		if(LEN(@search) <>0)
			set @search = @search + @and + ' TenSanPham like N''%' +@TenSanPham+'%'' ';
		else
			set @search = @and +  ' TenSanPham like N''%' +@TenSanPham+'%'' ';

	if(LEN(@TenLoaiSanPham)<>0)
		if(LEN(@search) <>0)
			set @search = @search + @and + ' [LOAISANPHAM].[TenLoaiSanPham] like N''%' + @TenLoaiSanPham +'%'' ';
		else
			set @search = @and + ' [LOAISANPHAM].[TenLoaiSanPham] like N''%' +@TenLoaiSanPham+ '%'' ';

	if(LEN(@DonGia)<>0)
		if(LEN(@search) <>0)
			set @search = @search + @and + @DonGia + ' ';
		else
			set @search = @and +  ' '+ @DonGia;

	if(LEN(@SoLuongTon)<>0)
		if(LEN(@search) <>0)
			set @search = @search +@and + @SoLuongTon + ' ';
		else
			set @search = @and +  ' ' + @SoLuongTon;

	if(LEN(@search) <>0)
		set @result = ' select * from [SANPHAM], [LOAISANPHAM]  where [SANPHAM].MaLoaiSanPham = [LOAISANPHAM].MaLoaiSanPham '+ @search;	
	else
		set @result =  ' select * from [SANPHAM], [LOAISANPHAM]  where [SANPHAM].MaLoaiSanPham = [LOAISANPHAM].MaLoaiSanPham ';

	exec (@result);

end
go

---exec FIND_SANPHAM '','',N'1','','';
-------------------------------------------------------------------------------------------------------------------
-----------------------------Phí---------------------------------------------------
--- drop proc ADD_PHI
print 'Them PHI'
if	not exists (select * from sys.objects where name='ADD_PHI' and type='P')
exec
('
		create proc ADD_PHI(@maphi nvarchar(10), @tenphi nvarchar(100))
		as
		begin
				insert into [PHI] values (@maphi, @tenphi);
		end
') 
Go

--- drop proc ADD_PHI_MOI
print 'Them PHI moi'
if	not exists (select * from sys.objects where name='ADD_PHI_MOI' and type='P')
exec
('
		create proc ADD_PHI_MOI( @tenphi nvarchar(100))
		as
		begin
				declare @maphi nvarchar(10) = null;
				exec @maphi = NEXT_F PHI;
				insert into [PHI] values (@maphi, @tenphi);
		end
') 
Go
--- drop proc REMOVE_PHI
print 'XoaPhi'
if	not exists (select * from sys.objects where name='REMOVE_PHI' and type='P')
exec
('
		create proc REMOVE_PHI(@maphi nvarchar(10))
		as
		begin
				delete [PHI] where [PHI].MaPhi = @maphi;
		end
') 
Go

---REMOVE_PHI 'PH001';

--- drop proc UPDATE_PHi
print 'Sua Phi'
if	not exists (select * from sys.objects where name='UPDATE_PHI' and type='P')
exec
('
		create proc UPDATE_PHI(@maphi nvarchar(10), @tenphi nvarchar(100))
		as
		begin
				update [PHI] set [PHI].TenPhi = @tenphi where [PHI].MaPhi = @maphi;
		end
') 
Go
---UPDATE_PHI 'PH002', 'Tao Moi Sua';

--- drop proc LAYPHIBYID
print 'lay Phi'
if	not exists (select * from sys.objects where name='LAYPHIBYID' and type='P')
exec
('
		create proc LAYPHIBYID(@maphi nvarchar(10))
		as
		begin
				select * from [PHI] where [PHI].MaPhi = @maphi;
		end
') 
Go

--- drop proc LAYTOANBOPHI
print 'lay Phi'
if	not exists (select * from sys.objects where name='LAYTOANBOPHI' and type='P')
exec
('
		create proc LAYTOANBOPHI
		as
		begin
				select * from [PHI];
		end
') 
Go


--- drop proc LAYTOANBOMAPHI

print 'LAYTOANBOMAPHI'
if	not exists (select * from sys.objects where name='LAYTOANBOMAPHI' and type='P')
exec
('
		create proc LAYTOANBOMAPHI
		as
		begin
				select [MaPhi] from [PHI];
		end
') 
Go

---exec LAYTOANBOMAPHI;
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------Phieu No---------------------------------------------------
--MaPhieuNo nvarchar(10),
--		MaPhieuBanHang nvarchar(10),
--		NgayNo smalldatetime,
--		SoTienTraNo money,
--		NgayTra smalldatetime,
--		SoTienTra money,
--		ConLai money

--- drop proc ADD_PHIEUNO
print 'THEM PHIEU NO'
if	not exists (select * from sys.objects where name='ADD_PHIEUNO' and type='P')
exec
('
		create proc ADD_PHIEUNO(@maphieuno nvarchar(10), @maphieubanhang nvarchar(10), @ngayno smalldatetime, @sotientrano money, @ngaytra smalldatetime, @sotientra money,  @conlai money)
		as
		begin
				
				insert into [PHIEUNO] values (@maphieuno, @maphieubanhang,CONVERT(smalldatetime, @ngayno),@sotientrano,convert(smalldatetime,@ngaytra),@sotientra,@conlai);
		end
') 
Go

-- drop proc ADD_PHIEUNO_MOI
 print 'Them Phieu No Moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_PHIEUNO_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_PHIEUNO_MOI (@maphieubanhang nvarchar(10), @ngayno smalldatetime, @sotientrano money, @ngaytra smalldatetime, @sotientra money,  @conlai money)
	AS
	declare @maphieu nvarchar(10) = null;
	exec @maphieu = NEXT_F PHIEUNO;
	BEGIN
		exec ADD_PHIEUNO @maphieu, @maphieubanhang, @ngayno, @sotientrano, @ngaytra, @sotientra, @conlai;
	END
');
GO

--insert into [PHIEUNO] values ('PN001','PB001','01/11/2013',100000,'04/11/2013',10000, 90000);
--exec ADD_PHIEUNO_MOI 'BH001','01/11/2013',100000,'04/11/2013',10000, 90000;

--- drop proc REMOVE_PHIEUNO
print 'xoa PHIEU NO'
if	not exists (select * from sys.objects where name='REMOVE_PHIEUNO' and type='P')
exec
('
		create proc REMOVE_PHIEUNO(@maphieuno nvarchar(10))
		as
		begin
				delete [PHIEUNO] where MaPhieuNo = @maphieuno;
		end
') 
Go
--- drop proc UPDATE_PHIEUNO
print 'Sua PHIEU NO'
if	not exists (select * from sys.objects where name='UPDATE_PHIEUNO' and type='P')
exec
('
		create proc UPDATE_PHIEUNO(@maphieuno nvarchar(10), @ngayno smalldatetime, @sotientrano money, @ngaytra smalldatetime, @sotientra money,  @conlai money)
		as
		begin
				update [PHIEUNO] set NgayNo = convert(smalldatetime,@ngayno), SoTienTraNo = @sotientrano, NgayTra = convert(smalldatetime,@ngaytra), SoTienTra = @sotientra , ConLai = @conlai where MaPhieuNo = @maphieuno;
		end
') 
Go

-- drop proc LAYPHIEUNOBYID
print 'Lay PHIEU NO'
if	not exists (select * from sys.objects where name='LAYPHIEUNOBYID' and type='P')
exec
('
		create proc LAYPHIEUNOBYID(@maphieuno nvarchar(10))
		as
		begin
			select * from [PHIEUNO] where MaPhieuNo = @maphieuno;
		end
') 
Go

print 'Lay PHIEU NO bang ma phieu ban hang'
if	not exists (select * from sys.objects where name='LAYPHIEUNOBYMAPHIEUBAN' and type='P')
exec
('
		create proc LAYPHIEUNOBYMAPHIEUBAN(@maphieubanhang nvarchar(10))
		as
		begin
			select * from [PHIEUNO] where MaPhieuBanHang = @maphieubanhang;
		end
') 
Go
--LAYPHIEUNOBYMAPHIEUBAN 'PB004'
--select * from [PHIEUNO] where MaPhieuBanHang =  'PB004'
print 'lay ma phieu no moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'GET_MAPHIEUNO_MAX' and type = 'P')
EXEC
('
	CREATE PROC GET_MAPHIEUNO_MAX
	AS
	declare @maphieu nvarchar(10) = null;
	exec @maphieu = NEXT_F PHIEUNO;
	BEGIN
		SELECT @maphieu as MaPhieuNo;
	END
');
GO

--select * from [PHIEUNO] p WHERE p.MaPhieuBanHang = 'PB004' and MaPhieuNo = (select MAX(MaPhieuNo) from [PHIEUNO] p WHERE p.MaPhieuBanHang = 'PB004')
--- drop proc LAY_PHIEUNO_CUOI_BY_PHIEUBANHANG
print 'Lay PHIEU NO cuoi boi ma phieu ban hang'
if	not exists (select * from sys.objects where name='LAY_PHIEUNO_CUOI_BY_PHIEUBANHANG' and type='P')
exec
('
		create proc LAY_PHIEUNO_CUOI_BY_PHIEUBANHANG(@maphieu nvarchar(10))
		as
		begin
			select * from [PHIEUNO] p WHERE p.MaPhieuBanHang = @maphieu and MaPhieuNo = (select MAX(MaPhieuNo) from [PHIEUNO] p WHERE p.MaPhieuBanHang = @maphieu);
		end
') 
Go
-- exec LAY_PHIEUNO_CUOI_BY_PHIEUBANHANG 'PB001'

 -- drop proc LAYPHIEUBANHANGTHEONGAY

 print 'lay ma phieu ban co ngay ban be hon ngay bao cao'

if	not exists (select * from sys.objects where name='LAYPHIEUBANHANGTHEONGAY' and type='P')
exec
('
		create proc LAYPHIEUBANHANGTHEONGAY(@ngayBan smalldatetime)
		as
		begin
				select DISTINCT b.MaPhieuBanHang from [PHIEUBANHANG] a , [PHIEUNO] b where a.MaPhieuBanHang = b.MaPhieuBanHang and a.NgayBan <= convert(smalldatetime,@ngayBan);
		end
') 
Go

--- exec LAYPHIEUBANHANGTHEONGAY '29/06/2013';

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------Phieu Ban Hang---------------------------------------------------
--     MaPhieuBanHang nvarchar(10),
--		NgayBan smalldatetime,
--		NgayThanhToan smalldatetime,
--		MaKhachHang nvarchar(10),
--		TongTien money,
--		constraint PK_PHIEUBANHANG primary key (MaPhieuBanHang)

--- drop proc ADD_PHIEUBANHANG
print 'them phieu ban hang'
if	not exists (select * from sys.objects where name='ADD_PHIEUBANHANG' and type='P')
exec
('
		create proc ADD_PHIEUBANHANG(@maphieubanhang nvarchar(10), @ngayban smalldatetime, @ngaythanhtoan smalldatetime,@makhachhang nvarchar(10), @tongtien money)
		as
		begin
				insert into [PHIEUBANHANG] values (@maphieubanhang, convert(smalldatetime,@ngayban),convert(smalldatetime,@ngaythanhtoan),@makhachhang, @tongtien);
		end
') 
Go

print 'Them Phieu ban hang moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_PHIEUBANHANG_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_PHIEUBANHANG_MOI (@ngayban smalldatetime, @ngaythanhtoan smalldatetime, @makhachhang nvarchar(10), @tongtien money)
	AS
	declare @maphieubanhang nvarchar(10) = null;
	exec @maphieubanhang = NEXT_F PHIEUBANHANG;
	BEGIN
		exec ADD_PHIEUBANHANG @maphieubanhang, @ngayban , @ngaythanhtoan, @makhachhang , @tongtien;
	END
');
GO


--- drop proc GET_MAPHIEUBANHANG_MAX
print 'Them Phieu ban hang moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'GET_MAPHIEUBANHANG_MAX' and type = 'P')
EXEC
('
	CREATE PROC GET_MAPHIEUBANHANG_MAX
	AS
	declare @maphieubanhang nvarchar(10) = null;
	exec @maphieubanhang = NEXT_F PHIEUBANHANG;
	BEGIN
		SELECT @maphieubanhang as MaPhieuBanHang;
	END
');
GO

--- drop proc REMOVE_PHIEUBANHANG
print 'xoa phieu ban hang'
if	not exists (select * from sys.objects where name='REMOVE_PHIEUBANHANG' and type='P')
exec
('
		create proc REMOVE_PHIEUBANHANG(@maphieubanhang nvarchar(10))
		as
		begin
				delete [PHIEUBANHANG]  where MaPhieuBanHang = @maphieubanhang ;
				exec REMOVE_CTPHIEUBANHANG @maphieubanhang;
		end
') 
Go

--- drop proc UPDATE_PHIEUBANHANG
print 'suaphieu ban hang'
if	not exists (select * from sys.objects where name='UPDATE_PHIEUBANHANG' and type='P')
exec
('
		create proc UPDATE_PHIEUBANHANG(@maphieubanhang nvarchar(10), @ngayban smalldatetime, @ngaythanhtoan smalldatetime, @tongtien money)
		as
		begin
				update [PHIEUBANHANG] set NgayBan = convert(smalldatetime,@ngayban) , NgayThanhToan = convert(smalldatetime,@ngaythanhtoan), TongTien = @TongTien where MaPhieuBanHang = @maphieubanhang;
		end
') 
Go
--select * from [PHIEUBANHANG] P where P.MaPhieuBanHang in (select [MaPhieuBanHang] from [PHIEUNO]) and p.MaKhachHang = 'KH003'
-- drop proc LAYPHIEUBANHANGBYID
print 'lay phieu ban hang'
if	not exists (select * from sys.objects where name='LAYPHIEUBANHANGBYID' and type='P')
exec
('
		create proc LAYPHIEUBANHANGBYID(@maphieubanhang nvarchar(10))
		as
		begin
				select * from [PHIEUBANHANG] where MaPhieuBanHang = @maphieubanhang;
		end
') 
Go
--LAY_PHIEUBANHANGB_TRACHAM_CUAKHACH 'KH004'
print 'lay phieu ban hang tra cham'
if	not exists (select * from sys.objects where name='LAY_PHIEUBANHANGB_TRACHAM_CUAKHACH' and type='P')
exec
('
		create proc LAY_PHIEUBANHANGB_TRACHAM_CUAKHACH(@makh nvarchar(10))
		as
		begin
				select * from [PHIEUBANHANG] P where P.MaPhieuBanHang in (select [MaPhieuBanHang] from [PHIEUNO]) and p.MaKhachHang = @makh;
		end
') 
Go

 -- drop proc 



-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------CTPhieu Ban Hang---------------------------------------------------
--MaCTPhieuBanHang nvarchar(10),
--		MaSanPham nvarchar(10),
--		MaPhieuBanHang nvarchar(10),
--		SoLuong int, 
--		DonGia money,
--		constraint PK_CTPHIEUBANHANG primary key (MaCTPhieuBanHang)

--- drop proc ADD_PHIEUBANHANG
print 'them ct phieu ban hang'
if	not exists (select * from sys.objects where name='ADD_CTPHIEUBANHANG' and type='P')
exec
('
		create proc ADD_CTPHIEUBANHANG(@mactphieuban nvarchar(10), @masp nvarchar(10), @maphieuban nvarchar(10), @soluong int, @dongia money)
		as
		begin
				insert into [CTPHIEUBANHANG] values (@mactphieuban,@masp,@maphieuban,@soluong,@dongia);
		end
') 
Go

print 'them ct phieu ban hang moi'
if	not exists (select * from sys.objects where name='ADD_CTPHIEUBANHANG_MOI' and type='P')
exec
('
		create proc ADD_CTPHIEUBANHANG_MOI(@masp nvarchar(10), @maphieuban nvarchar(10), @soluong int, @dongia money)
		AS
		declare @mactphieu nvarchar(10) = null;
		exec @mactphieu = NEXT_F CTPHIEUBANHANG;
		BEGIN
			exec ADD_CTPHIEUBANHANG @mactphieu, @masp, @maphieuban, @soluong, @dongia;
		END
') 
Go

--- drop proc REMOVE_PHIEUBANHANG
print 'xoa ct phieu ban hang'
if	not exists (select * from sys.objects where name='REMOVE_CTPHIEUBANHANG' and type='P')
exec
('
		create proc REMOVE_CTPHIEUBANHANG(@mactphieuban nvarchar(10))
		as
		begin
			delete [CTPHIEUBANHANG] where MaCTPhieuBanHang = @mactphieuban;
		end
') 
Go

--- drop proc LAYCTPHIEUBANHANGBYID
print 'lay ct phieu ban hang'
if	not exists (select * from sys.objects where name='LAYCTPHIEUBANHANGBYID' and type='P')
exec
('
		create proc LAYCTPHIEUBANHANGBYID(@mactphieuban nvarchar(10))
		as
		begin
			select * from [CTPHIEUBANHANG] where MaCTPhieuBanHang = @mactphieuban;
		end
') 
Go

--- drop proc LAYCTPHIEUBANHANGBYPHIEUBANHANGID

print 'lay ct phieu ban hang'
if	not exists (select * from sys.objects where name='LAYCTPHIEUBANHANGBYPHIEUBANHANGID' and type='P')
exec
('
		create proc LAYCTPHIEUBANHANGBYPHIEUBANHANGID(@maphieubanhang nvarchar(10))
		as
		begin
			select * from [CTPHIEUBANHANG],[PHIEUBANHANG] where [CTPHIEUBANHANG].MaPhieuBanHang = [PHIEUBANHANG].MaPhieuBanHang and [PHIEUBANHANG].MaPhieuBanHang = @maphieubanhang;
		end
') 
Go
---------------------- SQL PHIEU MUA HANG -------------------------------
print 'lay ma phieu mua hang moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'GET_MAPHIEUMUAHANG_MAX' and type = 'P')
EXEC
('
	CREATE PROC GET_MAPHIEUMUAHANG_MAX
	AS
	declare @maphieubanhang nvarchar(10) = null;
	exec @maphieubanhang = NEXT_F PHIEUMUA;
	BEGIN
		SELECT @maphieubanhang as MaPhieuMuaHang;
	END
');
GO

-- DROP PROC ADD_PHIEUMUAHANG
print 'them phieu mua hang'
if	not exists (select * from sys.objects where name='ADD_PHIEUMUAHANG' and type='P')
exec
('
		create proc ADD_PHIEUMUAHANG(@maphieumua nvarchar(10), @ngaymua smalldatetime, @ngaythanhtoan smalldatetime,@makhachhang nvarchar(10), @tongtien money)
		as
		begin
				insert into [PHIEUMUA] values (@maphieumua, convert(smalldatetime,@ngaymua),convert(smalldatetime,@ngaythanhtoan),@makhachhang, @tongtien);
		end
') 
Go

-- drop proc ADD_PHIEUMUAHANG_MOI
print 'Them Phieu mua hang moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_PHIEUMUAHANG_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_PHIEUMUAHANG_MOI (@ngaymua smalldatetime, @ngaythanhtoan smalldatetime, @makhachhang nvarchar(10), @tongtien money)
	AS
	declare @maphieumuahang nvarchar(10) = null;
	exec @maphieumuahang = NEXT_F PHIEUMUA;
	BEGIN
		exec ADD_PHIEUMUAHANG @maphieumuahang, @ngaymua , @ngaythanhtoan, @makhachhang , @tongtien;
	END
');
GO

-- drop proc ADD_CTPHIEUMUAHANG
print 'them ct phieu mua hang'
if	not exists (select * from sys.objects where name='ADD_CTPHIEUMUAHANG' and type='P')
exec
('
		create proc ADD_CTPHIEUMUAHANG(@mactphieumua nvarchar(10), @masp nvarchar(10), @maphieumua nvarchar(10), @soluong int, @dongia money)
		as
		begin
				insert into [CHITIETPHIEUMUA] values (@mactphieumua,@maphieumua,@masp,@soluong,@dongia);
		end
') 
Go

-- drop proc ADD_CTPHIEUMUAHANG_MOI
print 'them ct phieu mua hang moi'
if	not exists (select * from sys.objects where name='ADD_CTPHIEUMUAHANG_MOI' and type='P')
exec
('
		create proc ADD_CTPHIEUMUAHANG_MOI(@masp nvarchar(10), @maphieumua nvarchar(10), @soluong int, @dongia money)
		AS
		declare @mactphieu nvarchar(10) = null;
		exec @mactphieu = NEXT_F CHITIETPHIEUMUA;
		BEGIN
			exec ADD_CTPHIEUMUAHANG @mactphieu, @masp, @maphieumua, @soluong, @dongia;
		END
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------bao cao ton---------------------------------------------------
--create table BAOCAOTON
--(
--		MaBaoCaoTon nvarchar(10),
--		NgayBaoCao smalldatetime,
--		constraint PK_BAOCAOTON primary key (MaBaoCaoTon)
--)
------------ drop proc ADD_BAOCAOTON
print 'them bao cao ton'
if	not exists (select * from sys.objects where name='ADD_BAOCAOTON' and type='P')
exec
('
		create proc ADD_BAOCAOTON( @ngaybaocao smalldatetime)
		as
		begin
		declare @MaBaoCaoTon nvarchar(10);
		exec @MaBaoCaoTon = NEXT_F BAOCAOTON;
				insert into [BAOCAOTON] values(@MaBaoCaoTon,convert(smalldatetime,@ngaybaocao));
		end
') 
Go


---- drop proc GET_BAOCAOTON_MAX
print 'GET_BAOCAOTON_MAX'
if	not exists (select * from sys.objects where name='GET_BAOCAOTON_MAX' and type='P')
exec
('
		create proc GET_BAOCAOTON_MAX
		as
		begin
		declare @MaBaoCaoTon nvarchar(10);
		exec @MaBaoCaoTon = NEXT_F BAOCAOTON;
				select @MaBaoCaoTon as MaBaoCaoTon;
		end
') 
Go
--exec ADD_BAOCAOTON '15/06/2013';
------------ drop proc REMOVE_BAOCAOTON
print 'xoa bao cao ton'
if	not exists (select * from sys.objects where name='REMOVE_BAOCAOTON' and type='P')
exec
('
		create proc REMOVE_BAOCAOTON(@MaBaoCaoTon nvarchar(10))
		as
		begin
				exec REMOVE_CTBAOCAOTON @MaBaoCaoTon;
				delete [BAOCAOTON] where MaBaoCaoTon = @MaBaoCaoTon;
				
		end
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------ctbao cao ton---------------------------------------------------
--create table CTBAOCAOTON
--(
--		MaCTBaoCaoTon nvarchar(10),
--		MaBaoCaoTon nvarchar(10),
--		MaSanPham nvarchar(10),
--		TonDau int,
--		PhatSinh int,
--		TonCuoi int,
--		SoLuongBan int,
--		SoLuongMua int,
--		constraint PK_CTBAOCAOTON primary key (MaCTBaoCaoTon)
--)


------------ drop proc ADD_CTBAOCAOTON
print 'them CT bao cao ton'
if	not exists (select * from sys.objects where name='ADD_CTBAOCAOTON' and type='P')
exec
('
		create proc ADD_CTBAOCAOTON(@MaBaoCaoTon nvarchar(10),@MaSanPham nvarchar(10),@TonDau int, @PhatSinh int, @TonCuoi int,@SoLuongBan int, @SoLuongMua int )
		as
		begin
		declare @MaCTBaoCaoTon nvarchar(10);
		exec @MaCTBaoCaoTon = NEXT_F CTBAOCAOTON;
				insert into [CTBAOCAOTON] values(@MaCTBaoCaoTon,@MaBaoCaoTon ,@MaSanPham,@TonDau, @PhatSinh, @TonCuoi,@SoLuongBan, @SoLuongMua);
		end
') 
Go

---- drop proc REMOVE_CTBAOCAOTON

print 'xoa CT bao cao ton'
if	not exists (select * from sys.objects where name='REMOVE_CTBAOCAOTON' and type='P')
exec
('
		create proc REMOVE_CTBAOCAOTON(@MaBaoCaoTon nvarchar(10))
		as
		begin
				delete [CTBAOCAOTON] where MaBaoCaoTon = @MaBaoCaoTon;
		end
') 
Go

--- drop proc LAY_TONDAU_BY_MASANPHAM
print 'Lay LAY_TONDAU_BY_MASANPHAM'
if	not exists (select * from sys.objects where name='LAY_TONDAU_BY_MASANPHAM' and type='P')
exec
('
		create proc LAY_TONDAU_BY_MASANPHAM(@MaSanPham nvarchar(10))
		as
		begin
			select * from [CTBAOCAOTON] p WHERE p.MaSanPham = @MaSanPham and p.MaCTBaoCaoTon = (select MAX(b.MaCTBaoCaoTon) from [CTBAOCAOTON] b where b.MaSanPham = @MaSanPham);
		end
') 
Go

--- drop proc LAY_SOLUONGBAN
print 'Lay LAY_SOLUONGBAN'
if	not exists (select * from sys.objects where name='LAY_SOLUONGBAN' and type='P')
exec
('
		create proc LAY_SOLUONGBAN(@MaSanPham nvarchar(10) )
		as
		begin
			select * from [CTPHIEUBANHANG] p WHERE p.MaSanPham = @MaSanPham  ;
		end
') 
Go
-- exec LAY_SOLUONGBAN 'SP001';
--- drop proc LAY_SOLUONGMUA

print 'Lay LAY_SOLUONGMUA'
if	not exists (select * from sys.objects where name='LAY_SOLUONGMUA' and type='P')
exec
('
		create proc LAY_SOLUONGMUA(@MaSanPham nvarchar(10))
		as
		begin
			select * from [CHITIETPHIEUMUA] p WHERE p.MaSanPham = @MaSanPham;
		end
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------Bao Cao Cong No---------------------------------------------------
--		MaBaoCaoCongNo nvarchar(10),
--		NgayBaoCao smalldatetime,

--- drop proc ADD_BAOCAOCONGNO

print 'Them bao cao cong no'
if	not exists (select * from sys.objects where name='ADD_BAOCAOCONGNO' and type='P')
exec
('
		create proc ADD_BAOCAOCONGNO(@NgayBaoCao smalldatetime)
		as
		begin
		declare @MaBaoCaoCongNo nvarchar(10);
		exec @MaBaoCaoCongNo = NEXT_F BAOCAOCONGNO;
				insert into [BAOCAOCONGNO] values (@MaBaoCaoCongNo,convert(smalldatetime,@NgayBaoCao));
		end
') 
Go

--- drop proc GET_BAOCAOCONGNO_MAX
print 'lay ma bao cao cong no'
if	not exists (select * from sys.objects where name='GET_BAOCAOCONGNO_MAX' and type='P')
exec
('
		create proc GET_BAOCAOCONGNO_MAX
		as
		begin
		declare @MaBaoCaoCongNo nvarchar(10);
		exec @MaBaoCaoCongNo = NEXT_F BAOCAOCONGNO;
				select @MaBaoCaoCongNo as MaBaoCaoCongNo;
		end
') 
Go

-- exec GET_BAOCAOCONGNO_MAX;


--exec ADD_BAOCAOCONGNO 'BCCN005', '6/19/2013 04:08:55 AM';

--- drop proc REMOVE_BAOCAOCONGNO
print 'Xoa bao cao cong no'
if	not exists (select * from sys.objects where name='REMOVE_BAOCAOCONGNO' and type='P')
exec
('
		create proc REMOVE_BAOCAOCONGNO(@MaBaoCaoCongNo nvarchar(10))
		as
		begin
				exec REMOVE_CTBAOCAOCONGNO @MaBaoCaoCongNo;
				delete [BAOCAOCONGNO] where MaBaoCaoCongNo = @MaBaoCaoCongNo;
				
		end
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------ct Bao Cao Cong No---------------------------------------------------
--MaCTBaoCaoCongNo nvarchar(10),
--	MaBaoCaoCongNo nvarchar(10),
--	MaPhieuNo nvarchar(10),
--	TienNo money,

---- drop proc ADD_CTBAOCAOCONGNO
print 'Them ct bao cao cong no'
if	not exists (select * from sys.objects where name='ADD_CTBAOCAOCONGNO' and type='P')
exec
('
		create proc ADD_CTBAOCAOCONGNO(@MaBaoCaoCongNo nvarchar(10),@MaPhieuNo nvarchar(10),@TienNo money)
		as
		begin
		declare @MaCTBaoCaoCongNo nvarchar(10);
		exec @MaCTBaoCaoCongNo = NEXT_F CTBAOCAOCONGNO;
				insert into [CTBAOCAOCONGNO] values (@MaCTBaoCaoCongNo,@MaBaoCaoCongNo,@MaPhieuNo,@TienNo);
		end
') 
Go


---- drop proc REMOVE_CTBAOCAOCONGNO
print 'xoa ct bao cao cong no'
if	not exists (select * from sys.objects where name='REMOVE_CTBAOCAOCONGNO' and type='P')
exec
('
		create proc REMOVE_CTBAOCAOCONGNO(@MaBaoCaoCongNo nvarchar(10))
		as
		begin
				delete [CTBAOCAOCONGNO] where MaBaoCaoCongNo=@MaBaoCaoCongNo;
		end
') 
Go
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------PHieu Dich Vu---------------------------------------------------
			--MaPhieuDV nvarchar(10),
			--NgayDK smalldatetime,
			--NgayGiao smalldatetime,
			--MaKhachHang nvarchar(10),
			--TongTien money,

----- drop proc ADD_PHIEUDICHVU
print 'Them PHieu Dich Vu'
if	not exists (select * from sys.objects where name='ADD_PHIEUDICHVU' and type='P')
exec
('
		create proc ADD_PHIEUDICHVU(@MaPhieuDV nvarchar(10),
			@NgayDK smalldatetime,
			@NgayGiao smalldatetime,
			@MaKhachHang nvarchar(10),
			@TongTien money)
		as
		begin
				insert into [PHIEUDICHVU] values (@MaPhieuDV,convert(smalldatetime,@NgayDK),convert(smalldatetime,@NgayGiao),@MaKhachHang,@TongTien);
		end
') 
Go

-- drop proc ADD_PHIEUMUAHANG_MOI
print 'Them Phieu dich vu moi'
IF not exists (SELECT * FROM sys.objects WHERE name = 'ADD_PHIEUDICHVU_MOI' and type = 'P')
EXEC

('
	CREATE PROC ADD_PHIEUDICHVU_MOI (@ngaydk smalldatetime, @ngaygiao smalldatetime, @makhachhang nvarchar(10), @tongtien money)
	AS
	declare @maphieu nvarchar(10) = null;
	exec @maphieu = NEXT_F PHIEUDICHVU;
	BEGIN
		exec ADD_PHIEUDICHVU @maphieu, @ngaydk , @ngaygiao, @makhachhang , @tongtien;
	END
');
GO

--- drop proc GET_MAPHIEUBANHANG_MAX
print 'Lay ma phieu dich vu max'
IF not exists (SELECT * FROM sys.objects WHERE name = 'GET_MAPHIEUDICHVU_MAX' and type = 'P')
EXEC
('
	CREATE PROC GET_MAPHIEUDICHVU_MAX
	AS
	declare @maphieu nvarchar(10) = null;
	exec @maphieu = NEXT_F PHIEUDICHVU;
	BEGIN
		SELECT @maphieu as MaPhieuDichVu;
	END
');
GO
--exec ADD_PHIEUDICHVU 'PDV001','15/06/2013', '20/06/2013', 'KH001',10000;
---- drop proc REMOVE_PHIEUDICHVU
print 'xoa PHieu Dich Vu'
if	not exists (select * from sys.objects where name='REMOVE_PHIEUDICHVU' and type='P')
exec
('
		create proc REMOVE_PHIEUDICHVU(@MaPhieuDV nvarchar(10))
		as
		begin
				delete [PHIEUDICHVU] where MaPhieuDV= @MaPhieuDV;
				exec REMOVE_CHITIETPHIEUDICHVU @MaPhieuDV;
		end
') 
Go
--exec REMOVE_PHIEUDICHVU 'PDV001';

---- drop proc LAYPHIEUDICHVUBYID
print 'lay PHieu Dich Vu'
if	not exists (select * from sys.objects where name='LAYPHIEUDICHVUBYID' and type='P')
exec
('
		create proc LAYPHIEUDICHVUBYID(@MaPhieuDV nvarchar(10))
		as
		begin
				select * from [PHIEUDICHVU] where MaPhieuDV= @MaPhieuDV;
		end
') 
Go
 ---exec LAYPHIEUDICHVUBYID 'PDV001';
--- drop proc LAYCHITIETPHIEUDICHVUBYPHIEUDICHVUID
print 'lay ct PHieu Dich Vu'
if	not exists (select * from sys.objects where name='LAYCHITIETPHIEUDICHVUBYPHIEUDICHVUID' and type='P')
exec
('
		create proc LAYCHITIETPHIEUDICHVUBYPHIEUDICHVUID(@MaPhieuDV nvarchar(10))
		as
		begin
				select * from [PHIEUDICHVU],[CHITIETPHIEUDICHVU] where [PHIEUDICHVU].MaPhieuDV= [CHITIETPHIEUDICHVU].MaPhieuDV and [CHITIETPHIEUDICHVU].MaPhieuDV = @MaPhieuDV;
		end
') 
Go
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------ct Phieu DichVu---------------------------------------------------
-- @MaCTPhieuDV nvarchar(10),@MaPhieuDV nvarchar(10),@MaDV nvarchar(10), @SoLuong int,@DonGia money,

---drop proc ADD_CHITIETPHIEUDICHVU
print 'Them ctPHieu Dich Vu'
if	not exists (select * from sys.objects where name='ADD_CHITIETPHIEUDICHVU' and type='P')
exec
('
		create proc ADD_CHITIETPHIEUDICHVU(@MaCTPhieuDV nvarchar(10),@MaPhieuDV nvarchar(10),@MaDV nvarchar(10), @SoLuong int,@DonGia money)
		as
		begin
				insert into [CHITIETPHIEUDICHVU] values (@MaCTPhieuDV ,@MaPhieuDV ,@MaDV , @SoLuong ,@DonGia );
		end
') 
Go

-- drop proc ADD_PHIEUMUAHANG_MOI
print 'them ct phieu dich vu moi'
if	not exists (select * from sys.objects where name='ADD_CTPHIEUDICHVU_MOI' and type='P')
exec
('
		create proc ADD_CTPHIEUDICHVU_MOI(@MaPhieuDV nvarchar(10),@MaDV nvarchar(10), @SoLuong int,@DonGia money)
		AS
		declare @maphieu nvarchar(10) = null;
		exec @maphieu = NEXT_F CHITIETPHIEUDICHVU;
		BEGIN
			exec ADD_CHITIETPHIEUDICHVU @maphieu, @MaPhieuDV, @MaDV, @SoLuong, @DonGia;
		END
') 
Go

---drop proc REMOVE_CHITIETPHIEUDICHVU
print 'xoa ctPHieu Dich Vu'
if	not exists (select * from sys.objects where name='REMOVE_CHITIETPHIEUDICHVU' and type='P')
exec
('
		create proc REMOVE_CHITIETPHIEUDICHVU(@MaPhieuDV nvarchar(10))
		as
		begin
				delete [CHITIETPHIEUDICHVU] where MaPhieuDV = @MaPhieuDV;
		end
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------PHieu Gia Công---------------------------------------------------

--- drop proc GET_MAPHIEUGIACONG_MAX
print 'Lay Ma Phieu Gia Cong max'

if	not exists (select * from sys.objects where name='GET_MAPHIEUGIACONG_MAX' and type='P')
exec
('
		create proc GET_MAPHIEUGIACONG_MAX
		as
		begin
				declare @maPhieuGC nvarchar(10) = null;
				exec @maPhieuGC = NEXT_F PHIEUGIACONG;
				select @maPhieuGC as MaPhieuGC;
		end
') 
Go

--- exec GET_MAPHIEUGIACONG_MAX;




-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------PHieu Gia Công---------------------------------------------------

--- drop proc ADD_PHIEUGIACONG
print 'ADD_PHIEUGIACONG'


if	not exists (select * from sys.objects where name='ADD_PHIEUGIACONG' and type='P')
exec
('
		create proc ADD_PHIEUGIACONG (@MaPhieuGC nvarchar(10), @NgayGiaCong smalldatetime, @NgayNhanHang smalldatetime, @NgayThanhToan smalldatetime, @TongTien money, @MaTho nvarchar(10))
		as
		begin
				insert into [PHIEUGIACONG] values (@MaPhieuGC, convert(smalldatetime,@NgayGiaCong),convert(smalldatetime,@NgayNhanHang), CONVERT(smalldatetime,@NgayThanhToan),@TongTien,@MaTho);
		end
') 
Go


-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------ctPHieu Gia Công---------------------------------------------------

--- drop proc ADD_CTPHIEUGIACONG
print 'ADD_CTPHIEUGIACONG'


if	not exists (select * from sys.objects where name='ADD_CTPHIEUGIACONG' and type='P')
exec
('
		create proc ADD_CTPHIEUGIACONG (@MaPhieuGC nvarchar(10), @MaHangGC nvarchar(10), @SoLuong int, @DonGia money)
		as
		begin
		declare @MaCTPhieuGC nvarchar(10) = null;
		exec @MaCTPhieuGC = NEXT_F CTPHIEUGIACONG;
				insert into [CTPHIEUGIACONG] values (@MaCTPhieuGC, @MaPhieuGC,@MaHangGC,@SoLuong,@DonGia);
		end
') 
Go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------phiếu Chi---------------------------------------------------

--- drop proc GET_LAYMAPHIEUCHI_MAX 
print 'GET_LAYMAPHIEUCHI_MAX'

if	not exists (select * from sys.objects where name='GET_LAYMAPHIEUCHI_MAX' and type='P')
exec
('
		create proc GET_LAYMAPHIEUCHI_MAX
		as
		begin
		declare @MaPhieuChi nvarchar(10) = null;
		exec @MaPhieuChi = NEXT_F PHIEUCHI;
			select @MaPhieuChi as MaPhieuChi;
		end
') 
go

---- drop proc ADD_PHIEUCHI

print 'ADD_PHIEUCHI'

if	not exists (select * from sys.objects where name='ADD_PHIEUCHI' and type='P')
exec
('
		create proc ADD_PHIEUCHI (@MaPhieuChi nvarchar(10) , @NgayChi smalldatetime, @TongTien money)
		as
		begin
		insert into [PHIEUCHI] values (@MaPhieuChi, convert(smalldatetime,@NgayChi), @TongTien);
		end
') 
go

--- exec GET_LAYMAPHIEUCHI_MAX;
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------ChiTietPhieuChi---------------------------------------------------

---- drop proc ADD_CHITIETPHIEUCHI

print 'ADD_CHITIETPHIEUCHI'

if	not exists (select * from sys.objects where name='ADD_CHITIETPHIEUCHI' and type='P')
exec
('
		create proc ADD_CHITIETPHIEUCHI (@MaPhieuChi nvarchar(10) , @MaPhi nvarchar(10), @ThanhTien money)
		as
		begin
		declare @MaCTPhieuChi nvarchar(10) = null;
		exec @MaCTPhieuChi = NEXT_F CHITIETPHIEUCHI;
			insert into [CHITIETPHIEUCHI] values (@MaCTPhieuChi, @MaPhieuChi,@MaPhi, @ThanhTien);
		end
') 
go

-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------proc create id---------------------------------------------------
 --- drop function GENERATE
 CREATE FUNCTION GENERATE (@r NVARCHAR(200))
RETURNS nvarchar(10)
AS
	BEGIN
		DECLARE @q nvarchar(10) =null;
		DECLARE @Index INT;
		SET @Index = CAST(SUBSTRING(@r,LEN(@r) - 2 ,3) AS INT) + 1;
		if  @Index < 10
			SET @q =  SUBSTRING(@r,1,LEN(@r) - 3) +  '00' + CAST(@Index AS nvarchar(4))  ;
		else
		if @Index < 100
			SET @q =  SUBSTRING(@r,1,LEN(@r) - 3) +  '0' + CAST(@Index AS nvarchar(4))  ;
		else
			SET @q =  SUBSTRING(@r,1,LEN(@r) - 3) + CAST(@Index AS nvarchar(4))  ;
		RETURN @q;
END

GO
--drop function NEXT_F 

 create function NEXT_F (@table nvarchar(20))
 returns nvarchar(10)
 as 
 begin 
 declare @r nvarchar(10)=null;
 IF @table = 'THO'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([MaTho]) FROM [THO]),'0') ;
			IF @r = '0'
				RETURN 'TH001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'DICHVU'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([MaDV]) FROM [DICHVU]),'0') ;
			IF @r = '0'
				RETURN 'DV001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'KHACHHANG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([MaKhachHang]) FROM [KHACHHANG]),'0') ;
			IF @r = '0'
				RETURN 'KH001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'HANGGIACONG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([MaHangGC]) FROM [HANGGIACONG]),'0') ;
			IF @r = '0'
				RETURN 'GC001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'LOAISANPHAM'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([LOAISANPHAM].MaLoaiSanPham) FROM [LOAISANPHAM]),'0') ;
			IF @r = '0'
				RETURN 'LSP001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'SANPHAM'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([SANPHAM].MaSanPham) FROM [SANPHAM]),'0') ;
			IF @r = '0'
				RETURN 'SP001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHI'
		BEGIN
			SET @r  = ISNULL((SELECT MAX([PHI].MaPhi) FROM [PHI]),'0') ;
			IF @r = '0'
				RETURN 'PH001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUBANHANG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUBANHANG.MaPhieuBanHang) FROM PHIEUBANHANG),'0') ;
			IF @r = '0'
				RETURN 'PB001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CTPHIEUBANHANG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CTPHIEUBANHANG.MaCTPhieuBanHang) FROM CTPHIEUBANHANG),'0') ;
			IF @r = '0'
				RETURN 'CTB001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUMUA'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUMUA.MaPhieuMua) FROM PHIEUMUA),'0') ;
			IF @r = '0'
				RETURN 'PM001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CHITIETPHIEUMUA'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CHITIETPHIEUMUA.MaCTPhieuMua) FROM CHITIETPHIEUMUA),'0') ;
			IF @r = '0'
				RETURN 'CTM001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'DICHVU'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(DICHVU.MaDV) FROM DICHVU),'0') ;
			IF @r = '0'
				RETURN 'DV001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUDICHVU'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUDICHVU.MaPhieuDV) FROM PHIEUDICHVU),'0') ;
			IF @r = '0'
				RETURN 'PDV001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CHITIETPHIEUDICHVU'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CHITIETPHIEUDICHVU.MaCTPhieuDV) FROM CHITIETPHIEUDICHVU),'0') ;
			IF @r = '0'
				RETURN 'CTDV001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUGIACONG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUGIACONG.MaPhieuGC) FROM PHIEUGIACONG),'0') ;
			IF @r = '0'
				RETURN 'PGC001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CTPHIEUGIACONG'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CTPHIEUGIACONG.MaCTPhieuGC) FROM CTPHIEUGIACONG),'0') ;
			IF @r = '0'
				RETURN 'CTGC001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUNO'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUNO.MaPhieuNo) FROM PHIEUNO),'0') ;
			IF @r = '0'
				RETURN 'PN001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'PHIEUCHI'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(PHIEUCHI.MaPhieuChi) FROM PHIEUCHI),'0') ;
			IF @r = '0'
				RETURN 'PC001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CHITIETPHIEUCHI'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CHITIETPHIEUCHI.MaCTPhieuChi) FROM CHITIETPHIEUCHI),'0') ;
			IF @r = '0'
				RETURN 'CTPC001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CTBAOCAOCONGNO'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CTBAOCAOCONGNO.MaCTBaoCaoCongNo) FROM CTBAOCAOCONGNO),'0') ;
			IF @r = '0'
				RETURN 'CTBCCN001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'CTBAOCAOTON'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(CTBAOCAOTON.MaCTBaoCaoTon) FROM CTBAOCAOTON),'0') ;
			IF @r = '0'
				RETURN 'CTBCT001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'BAOCAOTON'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(BAOCAOTON.MaBaoCaoTon) FROM BAOCAOTON),'0') ;
			IF @r = '0'
				RETURN 'BCT001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
IF @table = 'BAOCAOCONGNO'
		BEGIN
			SET @r  = ISNULL((SELECT MAX(BAOCAOCONGNO.MaBaoCaoCongNo) FROM BAOCAOCONGNO),'0') ;
			IF @r = '0'
				RETURN 'BCCN001';
			ELSE
				EXEC @r =  GENERATE @r;
			END
RETURN @r;
end
go
--drop proc NEXT_ID
CREATE PROC NEXT_ID(@table NVARCHAR(200))
AS
BEGIN
	DECLARE @s nvarchar(10) = NULL;
	EXEC @s = NEXT_F @table;
	SELECT @s;
END
GO
---- drop proc InitData
 create proc InitData
 as
 begin 
 declare @i int = 0;
 declare @temp nvarchar(10) = null;
 declare @id int  = null;
 declare @ten nvarchar(40) = null;
 declare @diachi nvarchar(100) = null;
 declare @dongia money = null;
 declare @Loai int = null;
 while @i < 30
					 begin
					 exec @temp = NEXT_F 'THO';
					set @id=@i+1;
					set @ten=  N'SCS Thợ ' + cast(@i + 1 as nvarchar(4));
					set @diachi = N'Quận ' + cast(@i + 1 as nvarchar(4));
					exec ADD_THO @temp, @ten , @diachi;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END

set @i = 0;
while @i < 30
					 begin
					 exec @temp = NEXT_F 'DICHVU';
					set @id=@i+1;
					set @ten=  N'Dịch Vụ ' + cast(@i + 1 as nvarchar(4));
					set @dongia = 1000 *(@i +1);
					exec ADD_DICHVU @temp, @ten , @dongia;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END
set @i = 0;
while @i < 30
					 begin
					 exec @temp = NEXT_F 'KHACHHANG';
					set @id=@i+1;
					set @ten=  N'SCS Khách Hàng ' + cast(@i + 1 as nvarchar(4));
					set @diachi = N'Quận ' + cast(@i + 1 as nvarchar(4));
					if @i %3=0 or @i%5 = 0 or @i%7=0
							set @Loai = 1;
					else
							set @Loai = 0;
					exec ADD_KHACHHANG @temp, @ten , @diachi,@Loai;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END
set @i = 0;
while @i < 10
					 begin
					 exec @temp = NEXT_F 'HANGGIACONG';
					set @id=@i+1;
					set @ten=  N'Loại Hàng Gia Công ' + cast(@i + 1 as nvarchar(4));
					set @dongia = 100000 * (@i +1);
					exec ADD_HANGGIACONG @temp, @ten , @dongia;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END
set @i = 0;
while @i < 10
					 begin
					 exec @temp = NEXT_F 'LOAISANPHAM';
					set @id=@i+1;
					set @ten=  N'Loại Sản Phẩm ' + cast(@i + 1 as nvarchar(4));
					exec ADD_LOAISANPHAM @temp, @ten;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END
set @i = 0;
while @i < 10
					 begin
					 exec @temp = NEXT_F 'PHI';
					set @id=@i+1;
					set @ten=  N'Tên Phí ' + cast(@i + 1 as nvarchar(4));
					exec ADD_PHI @temp, @ten;
					IF @@ERROR <> 0
								BREAK;
					SET @I = @I + 1;
					END
return -1;
end
go
--Exec InitData


